package elite.sysfun.dbfun.dbexec.procedure;

import com.wisdge.dataservice.Result;
import elite.core.dataBase.DBMethod;
import elite.core.dataBase.sys.IECDS;
import elite.core.rightCtl.ControllerRT;
import elite.core.root.*;
import elite.core.root.PIn.SysDoInData;
import elite.core.staffInfo.OneStaff;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;

@Slf4j
public class ExecJSProcInfo {

    public Result DoProcedure(String spName, Map<String, Object> para, DataSource ds, String dbPool, LogObj logk) {
        Connection mConn = DBMethod.getConnectWait(ds, logk);
        if (mConn == null) {
            log.error(logk.getLogStr() + " 连接获取失败:" + dbPool);
            return new Result(-1, " 连接获取失败", "");
        }
        try {
            Result mResult = ExecProcedureFun(mConn, spName, para, dbPool, logk);
            log.info("Exec Prodcedure :" + spName + " Value:" + mResult.toString());
            DBMethod.CloseConnect(mConn, logk);
            return mResult;
        } catch (Exception e) {
            log.error("Exec Prodcedure :" + spName + " Exec Failed:" + e.toString(), e);
            DBMethod.CloseConnect(mConn, logk);
            return new Result(-1, " 执行存储过程失败:" + e.toString(), "");
        } catch (Error e) {
            log.error("Exec Prodcedure :" + spName + " Exec Failed:" + e.toString(), e);
            DBMethod.CloseConnect(mConn, logk);
            return new Result(-1, " 执行存储过程失败:" + e.toString(), "");
        }
    }

    @SuppressWarnings("unchecked")
    public Result DoProcedure(SysDoInData inData, String vDBKey) {
        LogObj logk = inData.getLogk();
        String mSPName = StaticMethod.getMapOneString(inData.getPara(), "name");
        Map<String, Object> mSPPara;
        if (inData.getPara().containsKey("paras")) {
            try {
                mSPPara = (Map<String, Object>) inData.getPara().get("paras");
            } catch (Exception e) {
                log.error(logk.getLogStr() + " 获取参数失败", e);
                mSPPara = new HashMap<String, Object>();
            } catch (Error e) {
                log.error(logk.getLogStr() + " 获取参数失败", e);
                mSPPara = new HashMap<String, Object>();
            }
        } else {
            mSPPara = new HashMap<String, Object>();
        }
        OneStaff mStaffInfo = inData.getStaff();
        // PublicRoot.getInstance().getStaffInfo(vKey,logk);
        if (mStaffInfo == null) {
            log.info(logk.getLogStr() + " Prodcedure XML Bound No Right:" + inData.getTokenId());
            return new Result(ErrorConst.NoRight, " no Right", "");
        }
        String dynId = StaticMethod.getMapOneString(inData.getPara(), "dynId");
        if (ControllerRT.getInstance().CheckRight(mStaffInfo.UseDBName(""), dynId, mStaffInfo, "", logk) < 0) {
            log.error(logk.getLogStr() + "越权执行SQL，DynID:" + dynId);
            return new Result(ErrorConst.LevelErr, logk.getLogStr() + "越权执行SQL ");
        }
        DataSource mDs = PublicVar.getInstance().getDB(mStaffInfo.UseDBName(vDBKey), 0, logk);
        if (mDs == null) {
            log.error(logk.getLogStr() + " 连接池获取失败:" + mStaffInfo.UseDBName(vDBKey));
            return new Result(-1, " 连接池获取失败", "");
        }
        return DoProcedure(mSPName, mSPPara, mDs, mStaffInfo.UseDBName(vDBKey), logk);
    }

    private String BuildCallString(LinkedList<SPParameter> vLinkList, String vSPName, String vDBKey, LogObj logk) {
        StringBuffer mTmp = new StringBuffer("{");
        if (vLinkList.size() > 0) {
            if (vLinkList.get(0).get_parameterName().equalsIgnoreCase("@RETURN_VALUE")) {
                mTmp.append("?=call ").append(vSPName).append("(");
                for (int i = 1; i < vLinkList.size(); i++) {
                    if (i == 1) {
                        mTmp.append("?");
                    } else {
                        mTmp.append(",?");
                    }
                }
            } else {
                mTmp.append(" call ").append(vSPName).append("(");
                for (int i = 0; i < vLinkList.size(); i++) {
                    if (i == 0) {
                        mTmp.append("?");
                    } else {
                        mTmp.append(",?");
                    }
                }
            }
            mTmp.append(")}");
        } else {
            if (PublicVar.getInstance().getOneDBPoolType(vDBKey, logk).equalsIgnoreCase("ORACLE")) {
                mTmp.append(" call ").append(vSPName).append("()}");
            } else {
                mTmp.append("?=call ").append(vSPName).append("()}");
            }

        }
        log.info("Exec ProcedureLib BuildCallStr:" + mTmp.toString());
        return mTmp.toString();
    }

    private String getSchemaDMDB(String url) {
        if (url == null) {
            return "";
        }
        url = url.trim();
        if (url.equals("")) {
            return "";
        }
        String[] urlParts = url.split("\\?");
        //没有参数
        if (urlParts.length == 1) {
            return "";
        }
        //有参数
        String[] params = urlParts[1].split("&");
        for (String param : params) {
            String[] keyValue = param.split("=");
            if ("schema".equalsIgnoreCase(keyValue[0])) {
                return keyValue[1];
            }
        }
        return "";
    }

    //@RETURN_VALUE
    private LinkedList<SPParameter> getProcedureParameter(Connection vConn, String vSPName, String vDBKey, LogObj logk) {
        String mySqlDB = "";
        int mysqlFlag = 0;
        String dbType = PublicVar.getInstance().getOneDBPoolType(vDBKey, logk);
        if (dbType.equalsIgnoreCase(IECDS.MySql) || dbType.equalsIgnoreCase(IECDS.OBMySql)) {
            try {
                mySqlDB = vConn.getCatalog();
                mysqlFlag = 1;
            } catch (Exception e) {
                log.error(logk.getLogStr() + " 获取DB Name失败");
            }
        }
        try {
            DatabaseMetaData metadata = vConn.getMetaData();
            String username = null;
            String PROCEDURE_CAT = null;
            int iPackFlag = 0;
            if (dbType.equalsIgnoreCase(IECDS.Oracle)) {
                username = metadata.getUserName().toUpperCase();
                if (vSPName.toUpperCase().indexOf(username + ".") == 0) {
                    vSPName = vSPName.substring(username.length() + 1);
                } else {
                    if (StaticMethod.StringSub(vSPName, 0, 2).equals("##")) {
                        int nIndex = vSPName.indexOf(".");
                        if (nIndex > 0) {
                            //username=vSPName.substring(0,nIndex);
                            PROCEDURE_CAT = StaticMethod.StringSub(vSPName, 2, nIndex);
                            vSPName = vSPName.substring(nIndex + 1);
                            iPackFlag = 1;
                        }
                    } else {
                        int nIndex = vSPName.indexOf(".");
                        if (nIndex > 0) {
                            username = vSPName.substring(0, nIndex);
                            vSPName = vSPName.substring(nIndex + 1);
                        }
                    }
                }
                vSPName = vSPName.toUpperCase();
                log.debug(logk.getLogStr() + " Oracle UserName:" + username + "  SPName:" + vSPName);
            } else if (dbType.equalsIgnoreCase(IECDS.DMSql)) {
                String conStr = vConn.toString();
                String[] tt = conStr.split(",");
                username = getSchemaDMDB(tt[0]);
                log.debug(logk.getLogStr() + "DM DB Shcmea:" + username);
            }
            LinkedList<SPParameter> mListPara = new LinkedList<SPParameter>();
            if (vSPName.trim().equals("")) {
                return mListPara;
            }
            ResultSet rs = metadata.getProcedureColumns(null, username, vSPName, null);//vSPName
            //			int iTmp=rs.getMetaData().getColumnCount();
            while (DBMethod.RecNext(rs)) {

                String curCat = (rs.getString("PROCEDURE_CAT") == null ? "" : rs.getString("PROCEDURE_CAT"));
                log.debug("PROCEDURE_CAT:" + curCat);
                if (mysqlFlag == 0 || (mysqlFlag == 1 && (mySqlDB.equalsIgnoreCase(curCat) || "".equals(curCat)))) {
                    if (iPackFlag == 1) {
                        String mTmpCat = rs.getString("PROCEDURE_CAT");
                        if (mTmpCat != null) {
                            if (mTmpCat.equalsIgnoreCase(PROCEDURE_CAT)) {
                                SPParameter mTmp = new SPParameter();
                                mTmp.set_parameterName(rs.getString("COLUMN_NAME"));
                                mTmp.set_ParameterDataType(rs.getInt("DATA_TYPE"));
                                mTmp.set_ParameterType(rs.getInt("COLUMN_TYPE"));
                                mTmp.set_parameterNull(rs.getInt("NULLABLE") == DatabaseMetaData.procedureNullable);
                                log.debug(logk.getLogStr() + " ColumnName:" + rs.getString("COLUMN_NAME") + "DATA_TYPE:" + rs.getInt("DATA_TYPE") + "COLUMN_TYPE:" + rs.getInt("COLUMN_TYPE"));
                                mListPara.add(mTmp);
                            }
                        }
                    } else {
                        SPParameter mTmp = new SPParameter();
                        mTmp.set_parameterName(rs.getString("COLUMN_NAME"));
                        mTmp.set_ParameterDataType(rs.getInt("DATA_TYPE"));
                        mTmp.set_ParameterType(rs.getInt("COLUMN_TYPE"));
                        mTmp.set_parameterNull(rs.getInt("NULLABLE") == DatabaseMetaData.procedureNullable);
                        log.debug(logk.getLogStr() + " ColumnName:" + rs.getString("COLUMN_NAME") + "DATA_TYPE:" + rs.getInt("DATA_TYPE") + "COLUMN_TYPE:" + rs.getInt("COLUMN_TYPE"));
                        mListPara.add(mTmp);
                    }
                }
                /*SPParameter mTmp = new SPParameter();
                mTmp.set_parameterName(rs.getString("COLUMN_NAME"));
                mTmp.set_ParameterDataType(rs.getInt("DATA_TYPE"));
                mTmp.set_ParameterType(rs.getInt("COLUMN_TYPE"));
                mTmp.set_parameterNull(rs.getInt("NULLABLE") == DatabaseMetaData.procedureNullable);
                LoggerWrite.info(logk.getLogStr() + " ColumnName:" + rs.getString("COLUMN_NAME") + "DATA_TYPE:" + rs.getInt("DATA_TYPE") + "COLUMN_TYPE:" + rs.getInt("COLUMN_TYPE"));
                mListPara.add(mTmp);*/
            }
            DBMethod.CloseResultSet(rs);
            return mListPara;
        } catch (Exception e) {
            log.error(logk.getLogStr() + " Get Procedure Parameter List Failed" + e.toString(), e);
            return null;
        }
    }

    private String getMapString(Map<String, Object> vParameter, String vName, LogObj logk) {
        Iterator<String> Item = vParameter.keySet().iterator();
        while (Item.hasNext()) {
            String mKey = Item.next();
            if (mKey.equalsIgnoreCase(vName)) {
                return String.valueOf(vParameter.get(mKey));
            }
        }
        log.error(logk.getLogStr() + " 存储过程 参数名：" + vName + " 值不存在");
        return "";
    }

    private Timestamp getParameterDate(Map<String, Object> vParameter, String vName, LogObj logk) {
        String mTmpStr = getMapString(vParameter, vName, logk).trim();
        if (mTmpStr.equals("")) {
            return null;
        }
        return StaticMethod.ValtoSqlTimeStamp(mTmpStr, logk);
		/*SimpleDateFormat format ;
  	    if (mTmpStr.length()==10){
  		  format= new SimpleDateFormat("yyyy-MM-dd");
  	    }else if (mTmpStr.trim().length()==16){
		  format= new SimpleDateFormat("yyyy-MM-dd HH:mm");
  	    }else if (mTmpStr.length()==8){
  		  format= new SimpleDateFormat("HH:mm:ss");
  	    }else if (mTmpStr.trim().length()==7 || mTmpStr.trim().length()==6){
	      format= new SimpleDateFormat("yyyy-MM");
  	    }else{
  		  format= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  	    }
 	    Date d;
 	    try {
			 d=new java.sql.Date(format.parse(mTmpStr).getTime());
 	    } catch (ParseException e) {
 		    LoggerWrite.error("Date Format Failed:"+e.toString(), e);
 		    return new Date(0);
 	    }
 	    return d;*/
    }

    //private int getMapStirngToInt(Map<String, Object> vParameter)
    private Result ExecProcedureFun(Connection vConn, String vSPName, Map<String, Object> vParameter, String vDBKey, LogObj logk) {

        String dbType = PublicVar.getInstance().getOneDBPoolType(vDBKey, logk);
        log.debug(logk.getLogStr() + " ExecProcedureFun dbType:" + dbType + " vSPName:" + vSPName + " vParameter:" + vParameter);

        if (dbType.equalsIgnoreCase(IECDS.PG) || dbType.equalsIgnoreCase("PostgreSQL")) {
            List<Object> mTmpResult = new ArrayList<Object>();

            JdbcTemplate jt = new JdbcTemplate(PublicVar.getInstance().getDB("DBMain", 0, logk));

            List<Map<String, Object>> selectParams = jt.queryForList("SELECT PROARGMODES, PROARGNAMES FROM PG_CATALOG.PG_PROC WHERE PROKIND = 'p' AND UPPER(PRONAME) = UPPER(?)", vSPName);
            log.debug("查询存储过程参数 :" + selectParams);

            if (selectParams.size() > 0) {

                // 查询存储过程参数
                String proargModes = selectParams.get(0).get("PROARGMODES").toString();
                String proargNames = selectParams.get(0).get("PROARGNAMES").toString();
                List<String> funTypes = mapStringToList(proargModes);
                List<String> funParams = mapStringToList(proargNames);
                log.debug("存储过程参数类型s=>" + funTypes);
                log.debug("存储过程参数列表s=>" + funParams);

                // 处理界面入参(将界面上入参Key全部转为小写)
                Map<String, Object> tParameter = new HashMap<>();
                for (String key : vParameter.keySet()) {
                    tParameter.put(key.toLowerCase(), vParameter.get(key));
                }

                // 遍历存储过程入参列表生成调用SQL
                StringBuilder mTmp = new StringBuilder();
                mTmp.append("call ").append(vSPName).append("(");

                for (int i = 0; i < funParams.size(); i++) {
                    String param_Name = funParams.get(i);
                    log.debug("--动态页面参数param_Name=>" + param_Name);
                    String param_Value = tParameter.containsKey(param_Name) ? tParameter.get(param_Name).toString() : "";

                    if (i == 0) {
                        mTmp.append("'").append(param_Value).append("'");
                    } else {
                        mTmp.append(",'").append(param_Value).append("'");
                    }
                }
                mTmp.append(")");
                log.debug("遍历存储过程入参列表生成调用SQL=>" + mTmp);

                // 执行调用
                Map<String, Object> execResMap = DBMethod.getFristRecordData(logk, vConn, mTmp.toString(), sqlTypeList.systemSql);
                log.debug("执行调用 Map结果:{}", execResMap);

                // 解析执行结果
                for (String key : execResMap.keySet()) {
                    log.debug("执行调用 Map Key:{}", key);
                    mTmpResult.add(execResMap.get(key).toString());
                }
                log.debug("执行调用 Result结果:{}", mTmpResult);

                return new Result(1, "", mTmpResult);
            } else {
                log.debug("Exec Procedure 未查询到存储过程参数 End");
                return new Result(1, "未查询到存储过程参数", mTmpResult);
            }

        } else {
            LinkedList<SPParameter> mLinkedList = getProcedureParameter(vConn, vSPName, vDBKey, logk);
            log.debug("Exec Procedure Build Paramet Begin");
            if (StaticMethod.StringSub(vSPName, 0, 2).equals("##")) {
                vSPName = StaticMethod.StringSub(vSPName, 2);
            }
            String mCallStr = BuildCallString(mLinkedList, vSPName, vDBKey, logk);
            log.debug("Exec Procedure Build Paramet Finsih");
            try {
                CallableStatement CallSP = vConn.prepareCall(mCallStr);
                int i, iCount = mLinkedList.size();
                for (i = 0; i < iCount; i++) {
                    SPParameter mTmp = mLinkedList.get(i);
                    if (mTmp.get_ParameterType() == DatabaseMetaData.procedureColumnIn) {
                        switch (mTmp.get_ParameterDataType()) {
                            case java.sql.Types.BIT:
                            case java.sql.Types.TINYINT:
                            case java.sql.Types.SMALLINT:
                            case java.sql.Types.INTEGER:
                            case java.sql.Types.BIGINT:
                                int mTmp123 = StaticMethod.StringToInt(getMapString(vParameter, mTmp.get_parameterName(), logk));
                                CallSP.setInt(i + 1, mTmp123);
                                break;
                            case java.sql.Types.FLOAT:
                            case java.sql.Types.REAL:
                            case java.sql.Types.DOUBLE:
                            case java.sql.Types.NUMERIC:
                            case java.sql.Types.DECIMAL:
                                float mTmp124 = StaticMethod.StringToFloat(getMapString(vParameter, mTmp.get_parameterName(), logk));
                                CallSP.setDouble(i + 1, mTmp124);
                                break;
                            case java.sql.Types.CHAR:
                            case java.sql.Types.VARCHAR:
                                CallSP.setString(i + 1, getMapString(vParameter, mTmp.get_parameterName(), logk));
                                break;
                            case java.sql.Types.DATE:
                            case java.sql.Types.TIME:
                            case java.sql.Types.TIMESTAMP:
                                /*Date mTmp125=;*/
                                CallSP.setTimestamp(i + 1, getParameterDate(vParameter, mTmp.get_parameterName(), logk));
                                break;
                            default:
                                log.info("Exec Procedure 未知道的类型" + mTmp.get_parameterName());
                                return new Result(-1, "Error Exec Product Failed:未知道的类型" + mTmp.get_parameterName(), "");
                        }
                    }
                    if (mTmp.get_ParameterType() == DatabaseMetaData.procedureColumnInOut || mTmp.get_ParameterType() == DatabaseMetaData.procedureColumnOut || mTmp.get_ParameterType() == DatabaseMetaData.procedureColumnReturn) {
                        CallSP.registerOutParameter(i + 1, mTmp.get_ParameterDataType());
                    }
                }
                log.debug("Exec Procedure Build Input Value Finish");
                CallSP.execute();
                log.debug("Exec Procedure Build Call Finish");
                List<Object> mTmpResult = new ArrayList<Object>();
                for (i = 0; i < iCount; i++) {
                    SPParameter mTmp = mLinkedList.get(i);
                    if (mTmp.get_ParameterType() == DatabaseMetaData.procedureColumnInOut || mTmp.get_ParameterType() == DatabaseMetaData.procedureColumnOut || mTmp.get_ParameterType() == DatabaseMetaData.procedureColumnReturn) {
                        switch (mTmp.get_ParameterDataType()) {
                            case java.sql.Types.BIT:
                            case java.sql.Types.TINYINT:
                            case java.sql.Types.SMALLINT:
                            case java.sql.Types.INTEGER:
                            case java.sql.Types.BIGINT:
                                log.debug(logk.getLogStr() + " PName:" + mTmp.get_parameterName() + " Value:" + CallSP.getInt(i + 1));
                                mTmpResult.add(CallSP.getInt(i + 1));
                                //	mTmpResult.put(mTmp.get_parameterName(), CallSP.getInt(i+1));
                                break;
                            case java.sql.Types.FLOAT:
                            case java.sql.Types.REAL:
                            case java.sql.Types.DOUBLE:
                            case java.sql.Types.NUMERIC:
                            case java.sql.Types.DECIMAL:
                                log.debug(logk.getLogStr() + " PName:" + mTmp.get_parameterName() + " Value:" + CallSP.getDouble(i + 1));
                                mTmpResult.add(CallSP.getDouble(i + 1));
                                //						mTmpResult.put(mTmp.get_parameterName(), CallSP.getDouble(i+1));
                                break;
                            case java.sql.Types.CHAR:
                            case java.sql.Types.VARCHAR:
                                log.debug(logk.getLogStr() + " PName:" + mTmp.get_parameterName() + " Value:" + CallSP.getString(i + 1));
                                mTmpResult.add(CallSP.getString(i + 1));
                                //mTmpResult.put(mTmp.get_parameterName(), CallSP.getString(i+1));
                                break;
                            case java.sql.Types.DATE:
                            case java.sql.Types.TIME:
                            case java.sql.Types.TIMESTAMP:
                                log.debug(logk.getLogStr() + " PName:" + mTmp.get_parameterName() + " Value:" + StaticMethod.getTimeString(CallSP.getDate(i + 1)));
                                mTmpResult.add(StaticMethod.getTimeString(CallSP.getDate(i + 1)));
                                //						mTmpResult.put(mTmp.get_parameterName(), StaticMethod.getTimeString(CallSP.getDate(i+1)));
                                break;
                            default:
                                log.debug(logk.getLogStr() + " PName:" + mTmp.get_parameterName() + " Value:" + CallSP.getString(i + 1));
                                mTmpResult.add(CallSP.getString(i + 1));
                                //mTmpResult.put(mTmp.get_parameterName(), CallSP.getString(i+1));
                                break;
                        }
                    }
                }
                log.debug(logk.getLogStr() + "Exec Procedure Call Result Build Finish Result: " + mTmpResult.toString());
                return new Result(1, "", mTmpResult);
            } catch (Exception e) {
                log.error(logk.getLogStr() + " Exec Procedure Failed:" + e.toString(), e);
                return new Result(-1, "Error Exec Procedure Failed:" + e.toString(), "");
            } catch (Error e) {
                log.error(logk.getLogStr() + " Exec Procedure Failed Error:" + e.toString(), e);
                return new Result(-1, "Error Exec Procedure Failed:" + e.toString(), "");
            }
        }
    }

    /*public String DoProcedureByBuild(String vSqlStr, String vKey, DataSource vDS, String vDBPool) {
        Document mDoc = StaticMethod.getXMLDom(vSqlStr);
        if (mDoc == null) {
            LoggerWrite.error(" Prodcedure XML Bound Faild:" + vSqlStr);
            return "Error   Xml Load Failed";// new ExecResult(-1,"99999",ex.toString());
        }
        Element mRootElement = mDoc.getRootElement();
        String mSPName = mRootElement.getChild("SPName").getText();
        Element mParas = mRootElement.getChild("Parameter");
        Connection mConn = DBMethod.getConnectWait(vDS);
        if (mConn == null) {
            return "Error" + "Connect Open Failed";//new ExecResult(-1,"-1","���Ӵ�ʧ��");
        }
        String mResult = ExecProcedureFun(mConn, mSPName, mParas, vDBPool);
        LoggerWrite.info("Exec Prodcedure :" + mSPName + " Value:" + mResult);
        DBMethod.CloseConnect(mConn);
        return mResult;
    }*/

    // Map字符串转Map对象
    private static Map<String, Object> mapStringToMap(String str) {
        str = str.substring(1, str.length() - 1);
        String[] strs = str.split(",");
        Map<String, Object> map = new HashMap<String, Object>();
        for (String string : strs) {
            String key = string.split("=")[0];
            String value = string.substring(string.indexOf("=") + 1);
            map.put(key.trim(), value.trim());
        }
        return map;
    }

    // Map字符串转List对象
    private static List<String> mapStringToList(String str) {
        str = str.substring(1, str.length() - 1);
        String[] strs = str.split(",");
        List<String> toList = new ArrayList<>();
        for (String string : strs) {
            String key = string.split("=")[0];
            toList.add(key.trim());
        }
        return toList;
    }
}
